package com.neusoft.elm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.elm.dao.BusinessDao;
import com.neusoft.elm.po.Admin;
import com.neusoft.elm.po.Business;
import com.neusoft.elm.util.DBUtil;

public class BusinessDaoImpl implements BusinessDao {
	Connection con = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	
	public List<Business> listBusiness(String businessName,String businessAddress){
		
		//String sql="SELECT * FROM business WHERE 1=1 ";
		StringBuffer sql = new StringBuffer("SELECT * FROM business WHERE 1=1 ");
		//商家名称
		if(businessName!=null&& !businessName.equals("")){
			sql.append(" AND businessName LIKE '%"+ businessName +"%' ");
		}
		if(businessAddress!=null&& !businessAddress.equals("")){
			sql.append(" AND businessAddress LIKE '%"+ businessAddress +"%' ");
		}
		
		List<Business> list=new ArrayList<>();
	
		try {
			//1.连接数据库
			con=DBUtil.getConnection();
			pst=con.prepareStatement(sql.toString());
			rs = pst.executeQuery();
			while(rs.next()){
				Business business = new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("BusinessExplain"));
				business.setBusinessId(rs.getInt("BusinessId"));
				business.setBusinessName(rs.getString("BusinessName"));
				business.setDeliveryPrice(rs.getDouble("DeliveryPrice"));
				business.setPassword(rs.getString("Password"));
				business.setStarPrice(rs.getDouble("StarPrice"));
				list.add(business);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.CloseAll(rs, pst, con);
		}
		return list;
	}

	@Override
	public int saveBusiness(String businessName) {
		int businessId = 0;
		String sql="INSERT INTO business (businessName ,password) VALUES (?,'123') ";
		try{
		//1.连接数据库
		con = DBUtil.getConnection();
		//设置返回自增长的值
		pst = con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
		pst.setString(1, businessName);
		pst.executeUpdate();
		//获取自动增长列(一行一列)
		rs = pst.getGeneratedKeys();
		if(rs.next()){
			businessId=rs.getInt(1);//参数是取回来第一列的值
		}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.CloseAll(rs, pst, con);
		}
		return businessId;
	}

	@Override
	public int removeBusiness(int businessId) {
		
		int result=0;
		String delFoodsql = "DELETE FROM food WHERE businessId=? ";
		String delBusinesssql = "DELETE FROM business WHERE businessId=? ";
		
		try{
			//1.连接数据库
			con = DBUtil.getConnection();
			//2.开启一个事务
			con.setAutoCommit(false);
			
			//删除食品信息
			pst = con.prepareStatement(delFoodsql);
			pst.setInt(1, businessId);
			pst.executeUpdate();
			//删除商家信息
			pst = con.prepareStatement(delBusinesssql);
			pst.setInt(1, businessId);
			result = pst.executeUpdate();
			
			//事务的提交
			con.commit();
			
			} catch (SQLException e) {
				e.printStackTrace();
				result = 0;
				try {
					//事务的回滚
					con.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}finally{
				DBUtil.CloseAll(rs, pst, con);
			}
		
		return result;
	}

	@Override
	public Business getBusinessByIdByPassword(Integer businessId, String password) {
		String sql="SELECT * FROM business WHERE businessId= ? AND password=? ";
		Business business = null;
		
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, businessId);
			pst.setString(2, password);
			rs = pst.executeQuery();
			
			//遍历执行结果
			while(rs.next()){
				business = new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("BusinessExplain"));
				business.setBusinessId(rs.getInt("BusinessId"));
				business.setBusinessName(rs.getString("BusinessName"));
				business.setDeliveryPrice(rs.getDouble("DeliveryPrice"));
				business.setPassword(rs.getString("Password"));
				business.setStarPrice(rs.getDouble("StarPrice"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.CloseAll(rs, pst, con);
		}
		return business;
	}

	@Override
	public Business showBusiness(Integer businessId) {
		String sql="SELECT * FROM business WHERE businessId=? ";
		Business business = null;
		
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, businessId);
			rs = pst.executeQuery();
			//遍历执行结果
			while(rs.next()){
				business = new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("BusinessExplain"));
				business.setBusinessId(rs.getInt("BusinessId"));
				business.setBusinessName(rs.getString("BusinessName"));
				business.setDeliveryPrice(rs.getDouble("DeliveryPrice"));
				business.setPassword(rs.getString("Password"));
				business.setStarPrice(rs.getDouble("StarPrice"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.CloseAll(rs, pst, con);
		}
		return business;
	}

	@Override
	public int updateBusiness(Business business) {
		int result = 0;
		String sql="UPDATE business SET businessName=?,businessAddress=?,businessExplain=?,starPrice=?,deliveryPrice=? WHERE businessId=? ";
		try{
		con = DBUtil.getConnection();
		pst = con.prepareStatement(sql);
		pst.setString(1,business.getBusinessName());
		pst.setString(2,business.getBusinessAddress());
		pst.setString(3,business.getBusinessExplain());
		pst.setDouble(4,business.getStarPrice());
		pst.setDouble(5,business.getDeliveryPrice());
		pst.setInt(6, business.getBusinessId());
		result = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.CloseAll(rs, pst, con);
		}
		return result;
	}

	@Override
	public int updateBusinessPassword(Integer businessId, String password) {
		int result = 0;
		String sql="UPDATE business SET password=? WHERE businessId=? ";
		try{
		con = DBUtil.getConnection();
		pst = con.prepareStatement(sql);
		pst.setString(1,password);
		pst.setInt(2,businessId);
		result = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.CloseAll(rs, pst, con);
		}
		return result;
	}
}
